using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient ;

namespace PointOfSale.Global
{
	public class clsDatabase
	{
		public clsDatabase()
		{
		}
		public SqlConnection ConnectToDatabase(string strDBName, string strServerIPAddress, string strSQLUserName , string strSQLPassword)
		{
			string strConnectionString = "server = " + strServerIPAddress + ";" + " uid = " + strSQLUserName + "; " + 
				"pwd = " + strSQLPassword +	"; database = " + strDBName + "; ";
			SqlConnection conn = new SqlConnection(strConnectionString);

			try
			{
				conn.Open();
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.ToString() );
			}
			return conn;
		}
		
		public SqlConnection ConnectToSQLServer(string strServerIPAddress, string strSQLUserName , string strSQLPassword)
		{
			SqlConnection conn = new SqlConnection("user id=" + strSQLUserName + ";" + 
				"password=" + strSQLPassword +";server=" + strServerIPAddress + ";" + 
				"Trusted_Connection=no;" +  
				"connection timeout=30");	

			try
			{
				conn.Open();
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.ToString());
			}
			return conn;
		}
	
		public void CloseDatabase(SqlConnection conn)
		{
			conn.Close();
		}

		public SqlDataAdapter GetDataToDataAdapter(string strSQL, SqlConnection conn)
		{								
			SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);			
			return da;
		}
		public DataSet GetDataToDataSet(string strSQL, SqlConnection conn, string  sTableName)
		{
			SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
			DataSet ds = new DataSet ();
			try 
			{
				da.Fill (ds, sTableName);			 				
			}
			catch (Exception e)
			{
				MessageBox.Show (e.Message);
			}
			return ds;
		}
		
				
		public DataGrid GetDataToDataGrid(DataSet ds, string sTablename)
		{ 
			DataGrid dataGrid1 = new DataGrid ();
			dataGrid1.SetDataBinding (ds,sTablename);
			return dataGrid1;
		}
		public DataSet DeleteRow(DataSet ds, string TableName, int RowIndex)
		{
			DataRow oRow = ds.Tables [TableName].Rows [RowIndex];
			oRow.Delete ();
			return ds;
		}
		
		public static SqlDataReader GetDataToDataReader( string  strSQL,SqlConnection conn )
		{
			SqlCommand myCommand = new SqlCommand(strSQL, conn);			
			SqlDataReader myReader = myCommand.ExecuteReader();
			return myReader;
		}
		public static DataTable GetDataToTable(String strSQL, SqlConnection conn)
		{
			DataTable dt = new DataTable();
			try
			{
				SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter(strSQL, conn);
				_sqlDataAdapter.Fill(dt);
			}
			catch(Exception exp)
			{
				MessageBox.Show(exp.ToString());
			}
			return dt;
		}
		public static void ExecuteSQL(string strSQL, SqlConnection conn)
		{				
			try
			{
				SqlCommand myCommand= new SqlCommand();		
				myCommand.Connection = conn;
				myCommand.CommandText = strSQL;
				myCommand.ExecuteNonQuery();
			}
			catch (Exception e1)
			{
				MessageBox.Show (e1.Message);
			}

		}
		public static bool IsExisted(string strSQL, SqlConnection conn)
		{
			DataTable tb = clsDatabase.GetDataToTable(strSQL, conn);
			return tb.Rows.Count == 0 ? false : true;
		}
		public static DataColumn CreateNewColumn( string sColumnType, string sColumnCaption,string sColumnName)
		{
			DataColumn dtColumn = new DataColumn();			
			dtColumn.DataType = System.Type.GetType("System." + sColumnType);
			dtColumn.ColumnName = sColumnName;
			dtColumn.Caption = sColumnCaption;			
			dtColumn.AutoIncrement = false;			
			dtColumn.ReadOnly = false;
			dtColumn.Unique = false;
			return dtColumn;
		}
	}
}
